package Action;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import Bean.NewsBean;
import Utils.Common;
import Utils.XmlReplace;

public class UserManagement{
	public String getUserList(Connection conn){
		String sql = "SELECT * FROM account ORDER BY username";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String xml = "";
		try{
			stmt = conn.prepareStatement(sql);
	
			rs = stmt.executeQuery();
			while(rs.next()){
				xml+="<user username=\""+rs.getString("username")+"\" lastlogin=\""+rs.getString("lastlogin")+"\"/>";
			}
			return Common.makeSuccessXML(xml);
		}catch (SQLException e) {
			e.printStackTrace();
			return Common.makeErrorXML("Error");
		}finally{
			if(rs!=null) try{rs.close();}catch (Exception e) {}
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
	}
	
	
	public String getUserSubmited(String userid, Connection conn){
		String sql = "SELECT COUNT(NewsId) AS total FROM news WHERE Poster=? AND `Status`=1";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, userid);
			rs = stmt.executeQuery();
			if(rs.next()){
				int total = rs.getInt("total");
				return Common.makeSuccessXML("<news total='"+total+"'/>"+getLastSubmited(userid,conn));
			}
			
		}catch (SQLException e) {
			e.printStackTrace();
			return Common.makeErrorXML("Error");
		}finally{
			if(rs!=null) try{rs.close();}catch (Exception e) {}
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
		return "";
	}
	
	public String getLastSubmited(String userid, Connection conn){
		String sql = "SELECT * FROM news WHERE DatePublished = (SELECT MAX(DatePublished) FROM news WHERE Poster=?)";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String xml = "";
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, userid);
			rs = stmt.executeQuery();
			
			if(rs.next()){
				NewsBean bean = new NewsBean();
				bean.setTitle(rs.getString("Title"));
				xml += "<item newsid=\""
						+ rs.getString("NewsId") + "\" " + "title=\""
						+ XmlReplace.escape(rs.getString("Title")) + "\" "
						+ "description=\""
						+ XmlReplace.escape(rs.getString("Summary"))
						+ "\" " + "createddate=\""
						+ rs.getString("DatePublished") + "\" " + "content=\""
						+ XmlReplace.escape(rs.getString("Content")) + "\" "
						+ "picturefilename=\""
						+ XmlReplace.escape(rs.getString("Picture"))
						+ "\" " 
						+ "homepic=\""
						+ XmlReplace.escape(rs.getString("HomePic"))
						+ "\" " 
						+ "catid=\"\" "
						+ "source=\""+XmlReplace.escape(rs.getString("Source"))+"\" "
						+ "link=\""+XmlReplace.escape(rs.getString("Link"))+"\" "
						+ "keyword=\""+XmlReplace.escape(rs.getString("Keyword"))+"\" "
						+ "tempcat=\""+XmlReplace.escape(rs.getString("TempCat"))+"\" "
						+" thumbnail=\""
						+ rs.getString("Thumbnail") + "\" "
						+" utitle=\""
						+ XmlReplace.escape(bean.getUTitle()) + "\""
						+" poster=\""
						+ XmlReplace.escape(bean.getPoster()) + "\""
						+"/>";
			}
			
		}catch (SQLException e) {
			e.printStackTrace();
			return "";
		}finally{
			if(rs!=null) try{rs.close();}catch (Exception e) {}
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
		return xml;
	}
}